﻿using Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DA
{
    public class BaseConnection
    {
        protected SqlConnection con = null;
        //protected string connect = @"Data Source=192.168.1.36;Initial Catalog=QuanLyGach;Integrated Security=True";
        

        public void openConnect()
        {
            try
            {
                if (con == null || con.State == ConnectionState.Closed)
                {

                    con = new SqlConnection(ConnectionString.getConnectionString());
                    con.Open();
                }

                //MessageBox.Show("Kết nối thành công");
            }
            catch(Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        public void closeConnect()
        {
            con.Close();
        }

        public DataSet callSP(string spName, List<StoreProcedureParameter>lstParamter)
        {
            DataSet ds = new DataSet();
            openConnect();
            using (SqlCommand cmd = new SqlCommand(spName, con))
            {

                cmd.CommandType = CommandType.StoredProcedure;
                
              if(lstParamter != null){

                foreach (StoreProcedureParameter item in lstParamter)
                {
                    cmd.Parameters.Add(item.key, item.type).Value = item.value;
                }
              }

                

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds);
                
            }
            closeConnect();
            return ds;
        }

        public int callSPExecute(string spName, List<StoreProcedureParameter> lstParamter)
        {
            int result = -1;
            openConnect();
            using (SqlCommand cmd = new SqlCommand(spName, con))
            {

                cmd.CommandType = CommandType.StoredProcedure;

                if (lstParamter != null)
                {

                    foreach (StoreProcedureParameter item in lstParamter)
                    {
                        cmd.Parameters.Add(item.key, item.type).Value = item.value;
                    }
                }

                result = cmd.ExecuteNonQuery();
            }
            closeConnect();
            return result;
        }

        public DataPacket layDuLieuBang(String sqlStatement)
        {
            openConnect();
            DataSet ds = new DataSet();
            SqlDataAdapter sqlDataAdapter;
            SqlCommand cmd = new SqlCommand(sqlStatement, con);
            sqlDataAdapter = new SqlDataAdapter(cmd);
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Fill(ds);
            closeConnect();
            return new DataPacket(sqlDataAdapter, ds);         
        }

        public DataSet layDuLieuTheoCauLenhSQL(String sql)
        {
            DataSet ds = new DataSet();
                      
            openConnect();

            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);
           
            adapter.Fill(ds, "ThongTinDonHang");
            closeConnect();
            return ds;  
        }

        public class StoreProcedureParameter
        {
            public String key { set; get; }
            public String value { set; get; }
            public SqlDbType type { set; get; }

            public StoreProcedureParameter(String key, String value, SqlDbType type)
            {
                this.key = key;
                this.value = value;
                this.type = type;
            }
        }

        public class DataPacket
        {
            public SqlDataAdapter sqlDataAdapter{set;get;}
            public DataSet ds { set; get; }

            public DataPacket(SqlDataAdapter sqlDataAdapter, DataSet ds)
            {
                this.sqlDataAdapter = sqlDataAdapter;
                this.ds = ds;
            }
        }
    }
}
